unit ServiceFbU;

interface

uses
  FireDAC.Comp.Client, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, Data.DB, FireDAC.VCLUI.Wait, FireDAC.Comp.UI, FireDAC.Phys.IBBase, FireDAC.Phys.FB,
  System.Generics.Collections, BusinessObjectsU, FireDAC.DApt;

type
  TServiceBase = class abstract
  private
    FFDConnection: TFDConnection;
    FDPhysFBDriverLink1: TFDPhysFBDriverLink;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
  protected
    function Connection: TFDConnection;
  public
    constructor Create(AConnection: TFDConnection);
    destructor Destroy;
  end;

  TAlunniService = class(TServiceBase)
  Strict Protected
  const
    SQL_ALUNNI = 'SELECT * FROM ALUNNI';
    SQL_ALUNNI_INQUADRAMENTO = 'select a.id, a.cognome||'' ''||a.nome as Nome, i.sezione, i.grado from alunni a join inquadramento i on ' +
      'a.id = i.id_alunno where (i.anno_start = :anno_start) and (i.anno_end = :anno_end) order by a.cognome, a.nome';
    SQL_TELEFONI_PARENTI = 'select k.descrizione, p.telefono1, p.telefono2 from parenti p join alunno_parente a on p.id = a.id_parente join ' +
      'parentela k on p.id_parentela = k.id where a.id_alunno = :id_alunno order by p.ordine';

  public
    function GetAlunniByIniziale(AFiltro: String): TObjectList<TAlunnoView>;
    function GetElencoAlunni(APeriodo: String): TObjectList<TAlunnoView>;
  end;

implementation

uses
  System.SysUtils;

{ TServiceBase }

function TServiceBase.Connection: TFDConnection;
begin
  result := FFDConnection;
end;

constructor TServiceBase.Create(AConnection: TFDConnection);
begin
  inherited Create;
  AConnection.Params.Clear;
  AConnection.Params.add('Database=ScuolaMaternaDB');
  AConnection.Params.add('User_Name=sysdba');
  AConnection.Params.add('Password=masterkey');
  AConnection.Params.add('CharacterSet=ISO8859_1');
  AConnection.Params.add('DriverID=FB');
  FFDConnection := AConnection;
end;

destructor TServiceBase.Destroy;
begin
  FFDConnection.Free;
  inherited;
end;

{ TAlunniService }

function TAlunniService.GetElencoAlunni(APeriodo: String): TObjectList<TAlunnoView>;
var
  query, qry: TFDQuery;
  Alunno: TAlunnoView;
  S: String;
  conta: ShortInt;
begin
  // elenco degli alunni nel peirodo scolastico
  query := TFDQuery.Create(nil);
  query.Connection := FFDConnection;
  query.SQL.add(SQL_ALUNNI_INQUADRAMENTO);
  query.ParamByName('anno_start').AsInteger := copy(APeriodo, 1, 4).ToInteger;
  query.ParamByName('anno_end').AsInteger := copy(APeriodo, 6, 4).ToInteger;

  // elenco dei numeri telefonici dei parenti legati agli alunni
  qry := TFDQuery.Create(nil);
  qry.Connection := FFDConnection;
  qry.SQL.add(SQL_TELEFONI_PARENTI);

  try
    query.Open;
    if not query.IsEmpty then
    begin
      result := TObjectList<TAlunnoView>.Create;
      // Scrive i dati dell'alunno
      while not query.eof do
      begin
        Alunno := TAlunnoView.Create;
        Alunno.ID := query.FieldByName('id').AsInteger;
        Alunno.Nome := query.FieldByName('Nome').AsString;
        Alunno.sezione := query.FieldByName('Sezione').AsString;
        Alunno.inquadramento := query.FieldByName('grado').AsString;
        // ottine i dati telefonici dei parenti
        // in questo caso al max solo 5 nri telefonici con il relativo grado di parentela
        qry.ParamByName('id_alunno').AsInteger := Alunno.ID;
        qry.Open;
        if not qry.IsEmpty then
        begin
          conta := 0;
          S := '';
          while not qry.eof and (conta < 4) do
          begin
            if not qry.FieldByName('telefono1').IsNull then
            begin
              S := S + qry.FieldByName('descrizione').AsString + ' (' + qry.FieldByName('telefono1').AsString;
              inc(conta);
              if qry.FieldByName('telefono2').IsNull then
                S := S + ') '
              else
              begin
                S := S + ' / ' + qry.FieldByName('telefono2').AsString + ') ';
                inc(conta);
              end;
            end;
            qry.Next;
          end;
        end;
        qry.Close;
        Alunno.Telefoni := S;
        Result.Add(Alunno);
        query.Next;
      end;
    end;

  finally
    query.Free;
    qry.Free;
  end;

end;

function TAlunniService.GetAlunniByIniziale(AFiltro: String): TObjectList<TAlunnoView>;
begin

end;

end.
